<?php

namespace common\service\export_business_excel_logic;

use common\model\EntityModel;
use common\model\NoteEntityRelationModel;
use common\model\ProjectModel;
use common\model\StatusModel;
use common\model\TaskModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use support\SingletonTrait;

class AssetList implements ExportLogicInterface
{
    use SingletonTrait;


    function process($param): Spreadsheet
    {
        $spreadsheet = new Spreadsheet();

        $project = model(ProjectModel::class)->find($param['project_id']);
        $episode = model(EntityModel::class)->find($param['episode_id']);
        $relateEpisodeType = $param['relate_episode_type'] ?? 'init';


        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->mergeCellsByColumnAndRow(1, 1, 19, 1);
        $worksheet->setCellValueByColumnAndRow(1, 1, "《{$project['name']}》{$episode['name']}资产表");
        $worksheet->getStyleByColumnAndRow(1, 1, 19, 1)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $worksheet->mergeCellsByColumnAndRow(1, 2, 19, 5);

        $assetModuleId = module_by_code('asset')['id'];
        $levelModuleId = module_by_code('level')['id'];
        $assetCount = model(EntityModel::class)->query("select count(module_id = 43 and json ->> '$.asset_category' = '角色' and json ->> '$.asset_grade' = 'S' or null) as spj,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '角色' and json ->> '$.asset_grade' = 'S' or null) as sj,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '角色' and json ->> '$.asset_grade' = 'A' or null) as aj,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '角色' and json ->> '$.asset_grade' = 'B' or null) as bj,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '角色' and json ->> '$.asset_grade' = 'C' or null) as cj,

       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '道具' and json ->> '$.asset_grade' = 'S' or null) as spd,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '道具' and json ->> '$.asset_grade' = 'S' or null) as sd,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '道具' and json ->> '$.asset_grade' = 'A' or null) as ad,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '道具' and json ->> '$.asset_grade' = 'B' or null) as bd,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '道具' and json ->> '$.asset_grade' = 'C' or null) as cd,


       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '场景' and json ->> '$.asset_grade' = 'S' or null) as spc,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '场景' and json ->> '$.asset_grade' = 'S' or null) as sc,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '场景' and json ->> '$.asset_grade' = 'A' or null) as ac,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '场景' and json ->> '$.asset_grade' = 'B' or null) as bc,
       count(module_id = {$assetModuleId} and json ->> '$.asset_category' = '场景' and json ->> '$.asset_grade' = 'C' or null) as cc,

       count(module_id = {$levelModuleId} and json ->> '$.level_grade' = 'S' or null)                                          as spg,
       count(module_id = {$levelModuleId} and json ->> '$.level_grade' = 'S' or null)                                          as sg,
       count(module_id = {$levelModuleId} and json ->> '$.level_grade' = 'A' or null)                                          as ag,
       count(module_id = {$levelModuleId} and json ->> '$.level_grade' = 'B' or null)                                          as bg,
       count(module_id = {$levelModuleId} and json ->> '$.level_grade' = 'C' or null)                                          as cg


from entity
where project_id = {$project['id']}
  and initial_episode_code = '{$episode['code']}'
  and module_id in ({$assetModuleId},{$levelModuleId})");


        $assetCountStr = sprintf(
            "角色：%d个（S+级%d个；S级%d个；A级%d个；B级%d个；C级%d个）；
道具：%d个（S+级%d个；S级%d个；A级%d个；B级%d个；C级%d个）；
场景：%d个（S+级%d个；S级%d个；A级%d个；B级%d个；C级%d个）
关卡：%d个（S+级%d个；S级%d个；A级%d个；B级%d个；C级%d个）",
            $assetCount[0]['spj'] + $assetCount[0]['sj'] + $assetCount[0]['aj'] + $assetCount[0]['bj'] + $assetCount[0]['cj'], $assetCount[0]['spj'], $assetCount[0]['sj'], $assetCount[0]['aj'], $assetCount[0]['bj'], $assetCount[0]['cj'],
            $assetCount[0]['spd'] + $assetCount[0]['sd'] + $assetCount[0]['ad'] + $assetCount[0]['bd'] + $assetCount[0]['cd'], $assetCount[0]['spd'], $assetCount[0]['sd'], $assetCount[0]['ad'], $assetCount[0]['bd'], $assetCount[0]['cd'],
            $assetCount[0]['spc'] + $assetCount[0]['sc'] + $assetCount[0]['ac'] + $assetCount[0]['bc'] + $assetCount[0]['cc'], $assetCount[0]['spc'], $assetCount[0]['sc'], $assetCount[0]['ac'], $assetCount[0]['bc'], $assetCount[0]['cc'],
            $assetCount[0]['spg'] + $assetCount[0]['sg'] + $assetCount[0]['ag'] + $assetCount[0]['bg'] + $assetCount[0]['cg'], $assetCount[0]['spg'], $assetCount[0]['sg'], $assetCount[0]['ag'], $assetCount[0]['bg'], $assetCount[0]['cg'],

        );

        $worksheet->setCellValueByColumnAndRow(1, 2, $assetCountStr);

        $dataColumns = [
            [
                'name' => '资产信息',
                'step_category_id' => [],
                'sub_col' => [
                    '集',
                    '场',
                    '等级',
                    '任务名',
                    '任务编码',
                    '类型',
                    '关联信息',
                ],
            ], [
                'name' => '美术',
                'step_category_id' => [2],
                'sub_col' => [
                    '是否有需求备注',
                    '任务状态',
                    '完成时间',
                ],
            ], [
                'name' => '模型',
                'step_category_id' => [3],
                'sub_col' => [
                    '是否有需求备注',
                    '任务状态',
                    '完成时间',
                ],
            ], [

                'name' => '绑定',
                'step_category_id' => [4],
                'sub_col' => [
                    '是否有需求备注',
                    '任务状态',
                    '完成时间',
                ],
            ], [

                'name' => '关卡',
                'step_category_id' => [7, 8, 9],
                'sub_col' => [
                    '是否有需求备注',
                    '任务状态',
                    '完成时间',
                ],
            ],

        ];

        $currentRow = 6;
        $columnIndex = 1;
        foreach ($dataColumns as $column) {
            $subColCount = count($column['sub_col']);
            $worksheet->mergeCellsByColumnAndRow($columnIndex, $currentRow, $columnIndex + $subColCount - 1, $currentRow);
            $worksheet->getStyleByColumnAndRow($columnIndex, $currentRow, $columnIndex + $subColCount - 1, $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $column['name']);

            $subColIndex = $columnIndex;
            foreach ($column['sub_col'] as $subColName) {
                $worksheet->setCellValueByColumnAndRow($subColIndex, $currentRow + 1, $subColName);
                $worksheet->getStyleByColumnAndRow($subColIndex, $currentRow + 1, $subColIndex, $currentRow + 1)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

                $subColIndex += 1;
            }
            $columnIndex += $subColCount;
        }
        $currentRow += 2;
        // 资产、关卡列表
        $episodeAssetLevelList = $this->getEpisodeAssetLevelList($project, $episode, $relateEpisodeType);

        if (empty($episodeAssetLevelList)) {
            return $spreadsheet;
        }

        $relateAssetLevelIdList = [];
        $assetLevelDesignIdList = [];
        // 关联资产查询
        foreach ($episodeAssetLevelList as &$assetLevel) {
            $moduleCode = module_by_id($assetLevel['module_id'])['code'];
            $assetLevel['json'] = json_decode($assetLevel['json'], true);
            $assetLevel['relate_asset_level'] = [];
            $assetLevel['grade'] = $assetLevel['json'][$moduleCode . '_grade'] ?? '-';
            $assetLevel['category'] = $assetLevel['json'][$moduleCode . '_category'] ?? '-';
            $assetLevelDesignIdList[] = $assetLevel['id'];
            if (!empty($assetLevel['json'][$moduleCode . '_design'])) {
                $assetLevelDesignIdList[] = $assetLevel['json'][$moduleCode . '_design'];
            }
            if ($moduleCode == 'level' && !empty($assetLevel['json'][$moduleCode . '_level'])) {
                foreach (explode(',', $assetLevel['json'][$moduleCode . '_level']) as $levelId) {
                    if (!in_array($levelId, $assetLevel['relate_asset_level'])) {
                        $assetLevel['relate_asset_level'][] = $levelId;
                    }
                }
            }
            foreach ($assetLevel['relate_asset_level'] as $assetLevelId) {
                if (!in_array($assetLevelId, $relateAssetLevelIdList)) {
                    $relateAssetLevelIdList[] = $assetLevelId;
                }
            }
        }
        $relateAssetLevelList = [];
        if (!empty($relateAssetLevelIdList)) {
            $relateAssetLevelList = model(EntityModel::class)->field('id,name')->where(['id' => ['IN', $relateAssetLevelIdList]])->select();
            $relateAssetLevelList = array_column($relateAssetLevelList, 'name', 'id');
        }

        // 镜头需求列表
        $assetLevelNoteListFilter = [
            'project_id' => $project['id'],
            'relate_module_code' => 'asset',
            'episode_code' => $episode['code'],
        ];
        $assetLevelNoteList = [];
//        $assetLevelNoteList = model(NoteEntityRelationModel::class)
//            ->where($assetLevelNoteListFilter)
//            ->group('relation_type, entity_id, step_category_id')
//            ->select();

        $episodeNote = false;
        $entityNote = [];
        $entityStepCategoryNote = [];
        foreach ($assetLevelNoteList as $assetLevelNote) {
            switch ($assetLevelNote['relation_type']) {
                case 'episode':
                    $episodeNote = true;
                    break;
                case 'entity':
                    $entityNote[] = $assetLevelNote['entity_id'];
                    break;
                case 'step_category':
                case 'step':
                    $entityStepCategoryNote[$assetLevelNote['entity_id'] . '#' . $assetLevelNote['step_category_id']] = true;
                    break;
            }
        }

        $assetLevelDesignIds = implode(',', $assetLevelDesignIdList);
        // 镜头任务完成情况
        $shotTaskList = model(TaskModel::class)
            ->where("id in (select max(id) from task where project_id = {$project['id']} and step_type='step' and entity_id in ({$assetLevelDesignIds}) group by entity_id,step_category_id )")
            ->field('entity_id,step_category_id,end_time,json,concat(entity_id,"#",step_category_id) as unique_id')
            ->select();
        $shotTaskList = array_column($shotTaskList, null, 'unique_id');

        unset($assetLevel);

        $statusList = model(StatusModel::class)->select();
        $statusList = array_column($statusList, 'name', 'id');
        foreach ($episodeAssetLevelList as $assetLevel) {
            $columnIndex = 1;
            foreach ($dataColumns as $column) {
                if (empty($column['step_category_id'])) {
                    if ($column['name'] === '资产信息') {
                        $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $assetLevel['initial_episode_code']);
                        $columnIndex += 1;
                        $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $assetLevel['showings_number']);
                        $columnIndex += 1;
                        $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $assetLevel['grade']);
                        $columnIndex += 1;
                        $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $assetLevel['name']);
                        $columnIndex += 1;
                        $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $assetLevel['code']);
                        $columnIndex += 1;
                        $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $assetLevel['category']);
                        $columnIndex += 1;
                        $relateName = '';
                        if (!empty($assetLevel['relate_asset_level'])) {
                            foreach ($assetLevel['relate_asset_level'] as $relateAssetLevelId) {
                                if (isset($relateAssetLevelList[$relateAssetLevelId])) {
                                    $relateName .= ',' . $relateAssetLevelList[$relateAssetLevelId];
                                }
                            }
                            $relateName = trim($relateName, ',');
                        }
                        $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $relateName);
                        $columnIndex += 1;
                    }
                } else {
                    $hasNote = false;
                    $moduleCode = module_by_id($assetLevel['module_id'])['code'];
                    $designId = $assetLevel['json'][$moduleCode . '_design'] ?? 0;
                    if ($episodeNote) {
                        $hasNote = true;
                    } elseif (!empty($entityNote[$designId])) {
                        $hasNote = true;
                    } else {
                        foreach ($column['step_category_id'] as $stepCategoryId) {
                            if (!empty($entityStepCategoryNote[$designId . '#' . $stepCategoryId])) {
                                $hasNote = true;
                                break;
                            }
                        }
                    }

                    $entityStepCategoryTask = null;
                    // 找到这些环节里 最后的一个环节工序任务
                    foreach ($column['step_category_id'] as $stepCategoryId) {

                        if (!empty($shotTaskList[$assetLevel['id'] . '#' . $stepCategoryId])) {
                            $entityStepCategoryTask = $shotTaskList[$assetLevel['id'] . '#' . $stepCategoryId];
                        } else {
                            if (!empty($shotTaskList[$designId . '#' . $stepCategoryId])) {
                                $entityStepCategoryTask = $shotTaskList[$designId . '#' . $stepCategoryId];
                            }
                        }
                    }

                    $taskStatus = '-';
                    $doneTime = '-';
                    if (!empty($entityStepCategoryTask)) {
                        $entityStepCategoryTask['json'] = json_decode($entityStepCategoryTask['json'], true);
                        if (!empty($statusList[$entityStepCategoryTask['json']['task_status'] ?? 0])) {
                            $taskStatus = $statusList[$entityStepCategoryTask['json']['task_status']];
                        }
                        if ($entityStepCategoryTask['end_time'] > 0) {
                            $doneTime = date("Y\\\m\\\d", $entityStepCategoryTask['end_time']);
                        }
                    }
                    $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $hasNote ? '是' : '否');
                    $columnIndex += 1;
                    $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $taskStatus);
                    $columnIndex += 1;
                    $worksheet->setCellValueByColumnAndRow($columnIndex, $currentRow, $doneTime);
                    $columnIndex += 1;
                }
            }
            $currentRow += 1;
        }
        return $spreadsheet;
    }

    /**
     * 查询剧集资产关卡列表
     * @param $project
     * @param $episode
     * @param string $relateEpisodeType
     * @return array|false|mixed|string
     * @throws \think\Exception
     */
    private function getEpisodeAssetLevelList($project, $episode, string $relateEpisodeType)
    {
        if ($relateEpisodeType == 'init') {
            $episodeAssetLevelListFilter = [
                'project_id' => $project['id'],
                'initial_episode_code' => $episode['code'],
                'module_id' => ['IN', [module_by_code('asset')['id'], module_by_code('level')['id']]],
            ];
        } else {
            $episodeShotListFilter = [
                'project_id' => $project['id'],
                'initial_episode_code' => $episode['code'],
                'module_id' => ['IN', [module_by_code('storyboard')['id']]],
            ];

            $shotList = model(EntityModel::class)->where($episodeShotListFilter)->field("json->>'$.storyboard_asset' asset,json->>'$.storyboard_level' level")->select();
            $assetLevelIdList = [];
            foreach ($shotList as $shot) {
                foreach (['asset', 'level'] as $field) {
                    $idList = explode(',', $shot[$field]);
                    foreach ($idList as $id) {
                        $id = intval($id);
                        if ($id > 0) {
                            $assetLevelIdList[$id] = $id;
                        }
                    }
                }
            }
            if (empty($assetLevelIdList)) {
                return [];
            }

            $episodeAssetLevelListFilter = [
                'id' => ['IN', array_values($assetLevelIdList)],
            ];
        }
        return model(EntityModel::class)
            ->where($episodeAssetLevelListFilter)
            ->select();
    }
}
